﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace MISAPP.Classify.DataAccess
{
    /// <summary>
    /// 序時統計表
    /// </summary>
    public class DA_WMG004:DA_Matrix
    {

        protected DateTime curr = DateTime.Now;

        public DataTable GetSummaryData()
        {
            string monthRange,sql;
            monthRange = getYMrange();
            sql = getSQLStatement(monthRange);
            return getData(sql, "rng");
        }

        /// <summary>
        /// 取得目前日期往前算12個月 
        /// </summary>
        /// <returns></returns>
        protected string getYMrange()
        {
            string sql;
            sql = @"SELECT
	YM AFTER
	,LEFT(REPLACE(convert(char(12),DATEADD(MONTH,-11,LEFT(YM,4) + '-' + RIGHT(YM,2) + '-' + '01'),111),'/',''),6) AS BEFORE	
    ,DATEADD(MONTH,-12,LEFT(YM,4) + '-' + RIGHT(YM,2) + '-' + '01') CURR
FROM(	SELECT MAX(A.年月)YM FROM WMG.dbo.WMG002 A)A";

            DataTable dtYM = getData(sql, "YM");
            curr = Convert.ToDateTime(dtYM.Rows[0]["CURR"].ToString());
            return string.Format("AND A.年月 BETWEEN '{1}' AND '{0}'"
                ,dtYM.Rows[0]["AFTER"].ToString()
                , dtYM.Rows[0]["BEFORE"].ToString());
        }

        protected string getFieldString()
        {
            StringBuilder sb = new StringBuilder();
            string fldString,mon;
            fldString = @"
,MAX(CASE WHEN A.年月 = '{0}{1}' THEN 
		CASE 
		WHEN A.OPN_AMT = 0 THEN '0%' 
		ELSE 			
			CAST(ROUND(A.OPN_AMT/A.DUE_AMT,4)*100 AS VARCHAR) + '%'
		END END) [{2}{1}]";
            DateTime d1 = DateTime.MaxValue;
            for (int i = 1; i <= 12; i++)
            {
                d1 = curr.AddMonths(i);
                mon = d1.Month.ToString().Length < 2 
                    ? string.Format("0{0}",d1.Month.ToString())
                    :d1.Month.ToString();
                sb.AppendFormat(fldString,d1.Year.ToString(),mon
                    ,Convert.ToString(d1.Year-1911));
            }
            return sb.ToString();
        }

        protected string getSQLStatement(string p_monRange)
        {
            return string.Format(@"
--序時統計表
SELECT
	CASE WHEN A.存款類別 IS NOT NULL THEN '' ELSE A.版塊別 END 版塊別	
	,CASE WHEN A.存款類別 IS NULL THEN '' ELSE A.存款類別 END 存款類別
	{0}
FROM(
	SELECT 
		'公庫' AS 版塊別
		,A.存款類別
		,A.年月
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 100
			WHEN A.存款類別 = '整存整付' THEN 110
			WHEN A.存款類別 = '存本取息' THEN 120
			WHEN A.存款類別 = '外匯定期' THEN 130
			ELSE 90 END ORD
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('13','12')
		--AND A.年月 BETWEEN '201004' AND '201103'
        {1}
		AND A.存款類別 IN ('定期存款','整存整付','存本取息','外匯定期')
	GROUP BY CUBE(A.年月,A.存款類別)

	UNION ALL

	SELECT 
		'財管' AS 版塊別
		,A.存款類別
		,A.年月
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 150
			WHEN A.存款類別 = '整存整付' THEN 160
			WHEN A.存款類別 = '存本取息' THEN 170
			WHEN A.存款類別 = '外匯定期' THEN 180
			ELSE 140 END ORD
		
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('A1')
		{1}
		AND A.存款類別 IN ('定期存款','整存整付','存本取息','外匯定期')	
	GROUP BY CUBE(A.年月,A.存款類別)

	UNION ALL

	SELECT 
		'信託' AS 版塊別
		,A.存款類別
		,A.年月
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 200
			WHEN A.存款類別 = '整存整付' THEN 210
			WHEN A.存款類別 = '存本取息' THEN 220
			WHEN A.存款類別 = '外匯定期' THEN 230
			ELSE 190 END ORD	
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('A5')
		{1}
		AND A.存款類別 IN ('定期存款','外匯定期')
	GROUP BY CUBE(A.年月,A.存款類別)

	UNION ALL

	SELECT 
		'企金' AS 版塊別
		,A.存款類別
		,A.年月
		,SUM(A.DUE_AMT) AS DUE_AMT
		,SUM(A.OPN_AMT) AS OPN_AMT
		,CASE 
			WHEN A.存款類別 = '定期存款' THEN 250
			WHEN A.存款類別 = '整存整付' THEN 260
			WHEN A.存款類別 = '存本取息' THEN 270
			WHEN A.存款類別 = '外匯定期' THEN 280
			ELSE 240 END ORD	
	FROM WMG.dbo.WMG002 A
	WHERE A.版塊別 IN ('C1')
		{1}
		AND A.存款類別 IN  ('定期存款','整存整付','存本取息','外匯定期')
	GROUP BY CUBE(A.年月,A.存款類別)

UNION ALL

SELECT 
	'總續存率' AS 版塊別
	,A.存款類別
	,A.年月
	,SUM(A.DUE_AMT) AS DUE_AMT
	,SUM(A.OPN_AMT) AS OPN_AMT
	,CASE 
		WHEN A.存款類別 = '定期存款' THEN 310
		WHEN A.存款類別 = '整存整付' THEN 320
		WHEN A.存款類別 = '存本取息' THEN 330
		WHEN A.存款類別 = '外匯定期' THEN 340
		ELSE 300 END ORD	
FROM WMG.dbo.WMG002 A 
WHERE 1=1
    {1}
	AND A.存款類別 IN  ('定期存款','整存整付','存本取息','外匯定期')
GROUP BY CUBE(A.年月,A.存款類別)


)A GROUP BY A.版塊別,A.存款類別,A.ORD
ORDER BY A.ORD", getFieldString() , p_monRange);
        }
    }
}
